-- @owner: @zhanghuan96
-- @date: 2024/11/20
-- @testpoint: 多次使用getxmltype，部分合理报错
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_xmlgen;
create extension gms_xmlgen;
set behavior_compat_options = 'bind_procedure_searchpath';
--step1:建表;expect:成功
drop table if exists t_getxmltype0013;
CREATE TABLE t_getxmltype0013 (
    id SERIAL PRIMARY KEY,
    binary_data BYTEA,
    bit_field BIT(8),
    xml_data XML,
    c4 varchar2(50)
);
INSERT INTO t_getxmltype0013 (binary_data, bit_field, xml_data,c4)
VALUES (
    decode('48656c6c6f20576f726c64', 'hex'),
    B'10101010',
    '<note><to>User</to><from>Admin</from><heading>Reminder</heading><body>Don''t forget the meeting!</body></note>',
    'O''Connor'
);
INSERT INTO t_getxmltype0013 values(default,null,null,null,'john&jane@example.com<123>-456""-7890');
--step2:多次使用getxmltype;expect:未使用restartquery，报错
DECLARE
    qryCtx gms_xmlgen.ctxHandle;
    result XMLTYPE;
BEGIN
    qryCtx := gms_xmlgen.newContext('SELECT * FROM t_getxmltype0013');
    result := gms_xmlgen.getXMLType(qryCtx);
    RAISE INFO 'INFO:XML Output: %', result::text;
	result := gms_xmlgen.getXMLType(qryCtx);
    RAISE INFO 'INFO:XML Output: %', result::text;
    gms_xmlgen.closeContext(qryCtx);
END;
/

--step3:多次使用getxmltype，使用restartquery;expect:成功
DECLARE
    qryCtx gms_xmlgen.ctxHandle;
    result XMLTYPE;
BEGIN
    qryCtx := gms_xmlgen.newContext('SELECT * FROM t_getxmltype0013');
    result := gms_xmlgen.getXMLType(qryCtx);
    RAISE INFO 'INFO:XML Output: %', result::text;
    gms_xmlgen.restartquery(qryCtx);
	result := gms_xmlgen.getXMLType(qryCtx);
    RAISE INFO 'INFO:XML Output: %', result::text;
    gms_xmlgen.restartquery(qryCtx);
	result := gms_xmlgen.getXMLType(qryCtx);
    RAISE INFO 'INFO:XML Output: %', result::text;
    gms_xmlgen.closeContext(qryCtx);
END;
/
--step4:清理环境;expect:成功
drop table if exists t_getxmltype0013;
drop extension if exists gms_xmlgen;